package org.qpcr.community.device.service;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.hswebframework.ezorm.core.param.Term;
import org.hswebframework.ezorm.rdb.operator.dml.query.SortOrder;
import org.hswebframework.reactor.excel.CellDataType;
import org.hswebframework.reactor.excel.ExcelHeader;
import org.hswebframework.reactor.excel.ReactorExcel;
import org.hswebframework.reactor.excel.spec.SheetSpec;
import org.hswebframework.web.api.crud.entity.QueryParamEntity;
import org.hswebframework.web.crud.service.GenericReactiveCrudService;
import org.qpcr.community.device.entity.CalibrationCmdEntity;
import org.qpcr.community.device.enums.CalibrationCommandTypeEnums;
import org.qpcr.community.device.enums.CalibrationCommandStatuEnums;
import org.qpcr.community.device.web.calibration.ResponseBody;
import org.qpcr.community.device.web.calibration.TemperatureCalibrationSequence;
import org.qpcr.community.device.web.request.*;
import org.qpcr.community.device.web.request.Module;
import org.qpcr.community.device.web.response.CalibrationResponse;
import org.springframework.core.annotation.Order;
import org.springframework.core.io.buffer.DataBufferFactory;
import org.springframework.core.io.buffer.DefaultDataBufferFactory;
import org.springframework.http.HttpHeaders;
import org.springframework.http.server.reactive.ServerHttpResponse;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestBody;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;
import java.util.function.Consumer;

@Service
@Slf4j
@RequiredArgsConstructor
@Order(10)
public class CalibrationCmdService extends GenericReactiveCrudService<CalibrationCmdEntity, String> {

    private final ReactiveMessageQueueService reactiveMessageQueueService;
    private final CalibrationResponseListenerService responseListenerService;

    /**
     * 发送校准指令
     */
    public Mono<String> sendCmd(CalibrationCmdSendRequest request) {
        if (!CalibrationCommandTypeEnums.validateType(request.getCommandType())) {
            return Mono.error(new IllegalArgumentException("无效的指令类型"));
        }
        CalibrationRequest calibrationRequest = CalibrationRequest.create(request);


//        return Mono.just("1353716f-d002-4aed-a1b2-e7d5021a0b12");
        return super.save(request)
                    .flatMap(saveResult -> sendCalibrationMessageToQueue(calibrationRequest));
    }

    /**
     * 发送校准响应
     */
    public Mono<String> sendResponse(CalibrationResponse response) {
        String responseQueueName = "device/calibration/response";
        String responseData = response.toJsonString();

        log.info("发送校准响应到队列: {}", responseQueueName);

        return reactiveMessageQueueService.sendMessage(responseQueueName, responseData)
                                          .map(result -> {
                                              log.info("校准响应已发送到队列，结果: {}", result);
                                              return response.getHeader().getMessageId();
                                          })
                                          .doOnError(error ->
                                                         log.error("发送校准响应失败: {}", error.getMessage(), error)
                                          );
    }

    /**
     * 根据消息ID获取校准指令
     */
    public Mono<CalibrationCmdEntity> getByMessageId(String messageId) {
        return super.createQuery()
                    .is(CalibrationCmdEntity::getMessageId, messageId)
                    .fetchOne()
                    .flatMap(entity -> {
                        if (entity == null) {
                            return Mono.empty();
                        }

                        // 检查是否超时（创建时间超过1分钟且状态为已发送或失败）
                        long currentTime = System.currentTimeMillis();
                        long createTime = entity.getCreateTime();
                        long timeDiff = currentTime - createTime;
                        long oneMinute = 6 * 60 * 1000; // 1分钟 = 60秒 * 1000毫秒

                        String status = entity.getStatus();
                        if (timeDiff > oneMinute &&
                            (CalibrationCommandStatuEnums.SEND.getType().equals(status) ||
                                CalibrationCommandStatuEnums.FAIL.getType().equals(status))) {
                            // 标记为超时
                            entity.setStatus(CalibrationCommandStatuEnums.TIMEOUT.getType());
                            return super.createUpdate()
                                        .is(CalibrationCmdEntity::getId, entity.getId())
                                        .set(CalibrationCmdEntity::getStatus, entity.getStatus())
                                        .execute()
                                        .thenReturn(entity);
                        }
                        return Mono.just(entity);
                    });
    }

    /**
     * 读取设备校准记录
     */
    public Mono<CalibrationCmdEntity> readRecord(String deviceId,String moduleCode) {
        return super.createQuery()
                    .is(CalibrationCmdEntity::getDeviceId, deviceId)
                    .is(CalibrationCmdEntity::getModuleCode, moduleCode)
                    .is(CalibrationCmdEntity::getStatus, CalibrationCommandStatuEnums.SUCCESS.getType())
                    .is(CalibrationCmdEntity::getCommandType, CalibrationCommandTypeEnums.CALIBRATION.getType())
                    .orderBy(SortOrder.desc(CalibrationCmdEntity::getCreateTime))
                    .fetchOne();
    }

    public Mono<List<CalibrationCmdEntity>> fluorescenceCalibrationReadRecord(String deviceId) {
        // 并行执行两个查询
        Mono<List<CalibrationCmdEntity>> writeCmdMono = getWriteCmd(deviceId);
        Mono<List<CalibrationCmdEntity>> analysisCmdMono = getFluorescenceCalibrationAnalysisCmd(deviceId);

        // 合并两个结果
        return Mono.zip(writeCmdMono, analysisCmdMono)
                   .map(tuple -> {
                       List<CalibrationCmdEntity> writeResults = tuple.getT1();
                       List<CalibrationCmdEntity> analysisResults = tuple.getT2();

                       // 合并两个列表
                       List<CalibrationCmdEntity> combinedResults = new ArrayList<>();
                       combinedResults.addAll(writeResults);
                       combinedResults.addAll(analysisResults);

                       // 按创建时间降序排序
                       combinedResults.sort((a, b) -> b.getCreateTime().compareTo(a.getCreateTime()));

                       // 返回所有合并后的记录
                       return combinedResults;
                   });
    }


    public Mono<List<CalibrationCmdEntity>> getWriteCmd(String deviceId){
        QueryParamEntity queryParam = new QueryParamEntity();
        queryParam.setPageIndex(0);
        queryParam.setPageSize(2);
        List<Term> terms = new ArrayList<>();
        Term term = new Term();
        term.and("device_id", deviceId);
        term.and("status", CalibrationCommandStatuEnums.SUCCESS.getType());
        term.and("command_type", CalibrationCommandTypeEnums.FLUORESCENCE_WRITE_PARAM.getType());
        terms.add(term);
        queryParam.setTerms(terms);
        queryParam.orderBy("create_time").desc();

        return super.queryPager(queryParam)
                    .map(pagerResult -> pagerResult.getData());
    }


    public Mono<List<CalibrationCmdEntity>> getFluorescenceCalibrationAnalysisCmd(String deviceId){
        QueryParamEntity queryParam = new QueryParamEntity();
        queryParam.setPageIndex(0);
        queryParam.setPageSize(1);
        List<Term> terms = new ArrayList<>();
        Term term = new Term();
        term.and("device_id", deviceId);
        term.and("status", CalibrationCommandStatuEnums.SUCCESS.getType());
        term.and("command_type", CalibrationCommandTypeEnums.PARAMETER_ANALYSIS.getType());
        terms.add(term);
        queryParam.setTerms(terms);
        queryParam.orderBy("create_time").desc();

        return super.queryPager(queryParam)
                    .map(pagerResult -> pagerResult.getData());
    }


    /**
     * 编辑备注
     */
    public Mono<Integer> editRemark(CalibrationCmdEntity entity) {
        return super.createUpdate()
                    .is(CalibrationCmdEntity::getId, entity.getId())
                    .set(CalibrationCmdEntity::getRemark, entity.getRemark())
                    .execute()
                    .thenReturn(1);
    }


    /**
     * 发送校准消息到队列（公共方法，供外部调用）
     * 与sendCmd使用相同的发送逻辑
     */
    public Mono<String> sendCalibrationMessage(CalibrationRequest calibrationRequest) {
        // 验证指令类型
        if (!CalibrationCommandTypeEnums.validateType(calibrationRequest.getBody().getCommandType())) {
            return Mono.error(new IllegalArgumentException("无效的指令类型"));
        }

        // 直接调用sendCalibrationMessageToQueue方法，与sendCmd使用相同的发送逻辑
        return sendCalibrationMessageToQueue(calibrationRequest);
    }


    public Mono<Void> exportTemperatureCalibrationResult(TemperatureCalibrationExportRequest exportRequest, ServerHttpResponse response) {
        // 设置响应头
        String name = "温度校准导出-" + Optional.ofNullable(exportRequest.getDeviceId()).orElse("");
        setupResponseHeaders(name, response);

        DataBufferFactory bufferFactory = new DefaultDataBufferFactory();

        return ReactorExcel
            .xlsxWriter()
            .multiSheet()
            .sheet(getDetailDataSheet(exportRequest.getDeviceId(), exportRequest.getModuleCode()))
            .sheet(getIndicatorDataSheet(exportRequest))
            .sheet(getCalibrationInfoSheet(exportRequest))
            .writeBytes(1024 * 1024)
            .map(bufferFactory::wrap)
            .as(response::writeWith)
            .doOnError(err -> log.error("导出温度校准结果失败: {}", err.getMessage()));
    }


    public Mono<Void> exportFluorescenceCalibrationDataSingle(FluorescenceCalibrationDataExportSingleRequest req, String deviceId, String moduleId, ServerHttpResponse response) {
        // 设置响应头
        String name = "荧光校准导出-" + deviceId + "-" + moduleId + "模组";
        setupResponseHeaders(name, response);
        DataBufferFactory bufferFactory = new DefaultDataBufferFactory();
        return ReactorExcel
            .xlsxWriter()
            .multiSheet()
            .sheet(getCalibrationResultsSheetSingle(req))
            .sheet(getRawDataSheetSingle(req))
            .sheet(getCalibrationAfterDataSheetSingle(req))
            .sheet(getFAMDataSheetSingle(req))
            .sheet(getVICDataSheetSingle(req))
            .sheet(getROXDataSheetSingle(req))
            .sheet(getCy5DataSheetSingle(req))
            .writeBytes(1024 * 1024)
            .map(bufferFactory::wrap)
            .as(response::writeWith)
            .doOnError(err -> log.error("导出荧光校准结果失败: {}", err.getMessage()));
    }


    public Mono<Void> exportFluorescenceCalibrationDataWhole(FluorescenceCalibrationDataExportWholeRequest req, String deviceId, String currentCalibrationParam, String beforeCalibrationParam, ServerHttpResponse response) {
        System.out.println(JSONUtil.toJsonStr(req));
        // 设置响应头
        String name = "荧光校准导出-" + deviceId + "-";
        setupResponseHeaders(name, response);
        DataBufferFactory bufferFactory = new DefaultDataBufferFactory();
        return ReactorExcel
            .xlsxWriter()
            .multiSheet()
            .sheet(getWholeMachineDataSheet(req, currentCalibrationParam, beforeCalibrationParam))
            .sheet(getAdjusted1DataSheet(req))
            .sheet(getAdjusted2DataSheet(req))
            .sheet(getAdjusted3DataSheet(req))
            .sheet(getAdjusted4DataSheet(req))
            .writeBytes(1024 * 1024)
            .map(bufferFactory::wrap)
            .as(response::writeWith)
            .doOnError(err -> log.error("导出荧光校准结果失败: {}", err.getMessage()));
    }


    /*******************************************************************************************************************/
    /*******************************************************Private方法**************************************************/
    /*******************************************************************************************************************/


    /**
     * 响应式发送校准消息到队列
     * 此方法与sendCmd使用完全相同的发送逻辑
     */
    private Mono<String> sendCalibrationMessageToQueue(CalibrationRequest calibrationRequest) {
        String queueName = calibrationRequest.generateQueueName();
        String messageData = calibrationRequest.toJsonString();

        log.info("准备发送校准消息到队列: {}", queueName);
        log.info("发送到队列的JSON数据: {}", messageData);

        // 使用与sendCmd相同的发送逻辑
        return reactiveMessageQueueService.sendMessage(queueName, messageData)
                                          .map(result -> {
                                              log.info("校准消息已成功发送到队列: {}, 结果: {}", queueName, result);
                                              return calibrationRequest.getHeader().getMessageId();
                                          })
                                          .doOnSuccess(messageId ->
                                                           log.info("校准消息发送成功，messageId: {}", messageId)
                                          )
                                          .doOnError(error ->
                                                         log.error("校准消息发送失败: {}", error.getMessage(), error)
                                          );
    }


    /**
     * 设置响应头
     */
    private void setupResponseHeaders(String name, ServerHttpResponse response) {
        try {
            String currentTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy年MM月dd日HH时mm分ss秒"));
            String fileName = URLEncoder.encode(name + "-" + currentTime + ".xlsx", StandardCharsets.UTF_8.displayName());
            response.getHeaders().set(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + fileName);
            response
                .getHeaders()
                .set(HttpHeaders.CONTENT_TYPE, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        } catch (Exception e) {
            log.warn("设置响应头失败: {}", e.getMessage());
        }
    }

    /**
     * 获取详细数据Sheet配置
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getDetailDataSheet(String deviceId,String moduleCode) {

        CalibrationCmdSendRequest req = new CalibrationCmdSendRequest();
        req.setDeviceId(deviceId);
        req.setModuleCode(moduleCode);
        req.setCommandType(CalibrationCommandTypeEnums.EXPORT_DETAIL_DATA.getType());
        req.setCommandTypeName(CalibrationCommandTypeEnums.EXPORT_DETAIL_DATA.getTypeName());

        // 调用发送并轮询结果，成功则解析为 rows
        Flux<Map<String, Object>> detailRows = Mono.defer(() -> sendCmd(req))
            .flatMapMany(messageId -> Flux
                .interval(java.time.Duration.ofMillis(500))
                .flatMap(tick -> getByMessageId(messageId))
                .takeUntil(entity -> !CalibrationCommandStatuEnums.SEND.getType().equals(entity.getStatus()))
                .filter(entity -> !CalibrationCommandStatuEnums.SEND.getType().equals(entity.getStatus()))
                .take(1)
                .flatMap(entity -> {
                    String status = entity.getStatus();
                    if (CalibrationCommandStatuEnums.SUCCESS.getType().equals(status)) {
                        try {
                            JSONObject resp = JSON.parseObject(entity.getResponseBody());
                            if (resp == null) {
                                return Flux.empty();
                            }
                            JSONObject body = resp.getJSONObject("body");
                            if (body == null) {
                                return Flux.empty();
                            }
                            JSONArray result = body.getJSONArray("result");
                            if (null != result && result.size() > 0){
                                return Flux.fromIterable(result)
                                           .map(obj -> (JSONObject) obj)
                                           .map(obj -> {
                                               Map<String, Object> row = new HashMap<>();
                                               if (obj.containsKey("timeSeconds")) row.put("time", obj.getDoubleValue("timeSeconds"));
                                               if (obj.containsKey("resistanceOhm")) row.put("res", obj.getDoubleValue("resistanceOhm"));
                                               if (obj.containsKey("surfaceResistanceXOhm")) row.put("rsurX", obj.getDoubleValue("surfaceResistanceXOhm"));
                                               if (obj.containsKey("surfaceResistanceYOhm")) row.put("rsurY", obj.getDoubleValue("surfaceResistanceYOhm"));
                                               if (obj.containsKey("surfaceResistanceZOhm")) row.put("rsurZ", obj.getDoubleValue("surfaceResistanceZOhm"));
                                               if (obj.containsKey("realTemperatureXCelsius")) row.put("realTempX", obj.getDoubleValue("realTemperatureXCelsius"));
                                               if (obj.containsKey("realTemperatureYCelsius")) row.put("realTempY", obj.getDoubleValue("realTemperatureYCelsius"));
                                               if (obj.containsKey("realTemperatureZCelsius")) row.put("realTempZ", obj.getDoubleValue("realTemperatureZCelsius"));
                                               return row;
                                           });
                            }
                            return Flux.empty();
                        } catch (Exception e) {
                            log.warn("解析导出详细数据响应失败: {}", e.getMessage());
                            return Flux.empty();
                        }
                    }
                    return Flux.empty();
                })
            )
            .onErrorResume(err -> {
                log.error("导出详细数据发送或查询失败: {}", err.getMessage());
                return Flux.empty();
            });

        return sheet -> {
            sheet.name("详细数据")
                 .header(new ExcelHeader("time", "Time / s", CellDataType.NUMBER))
                 .header(new ExcelHeader("res", "Res(ohm)", CellDataType.NUMBER))
                 .header(new ExcelHeader("rsurX", "Rsur_X(ohm)", CellDataType.NUMBER))
                 .header(new ExcelHeader("rsurY", "Rsur_Y(ohm)", CellDataType.NUMBER))
                 .header(new ExcelHeader("rsurZ", "Rsur_Z(ohm)", CellDataType.NUMBER))
                 .header(new ExcelHeader("realTempX", "Real_Temp_X(Celsius Degree)", CellDataType.NUMBER))
                 .header(new ExcelHeader("realTempY", "Real_Temp_Y(Celsius Degree)", CellDataType.NUMBER))
                 .header(new ExcelHeader("realTempZ", "Real_Temp_Z(Celsius Degree)", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 7))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 0,4, 13))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 5,7, 25));
        };
    }

    /**
     * 获取指标数据Sheet配置
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getIndicatorDataSheet(TemperatureCalibrationExportRequest req) {
        List<Map<String, Object>> indicatorList = new ArrayList<>();
        String resp = req.getResponseBody();
        if (StrUtil.isNotBlank(resp)){
            CalibrationResponse respObj = JSON.parseObject(resp, CalibrationResponse.class);
            JSONObject jsonResult = respObj.getBody().getJsonResult();
            req.setCurrentCalibrationParam(jsonResult.getString("calibrationParam"));

            JSONArray metricData = jsonResult.getJSONArray("metricData");
            for (int i = 0; i < metricData.size(); i++) {
                Map<String, Object> mp = metricData.getObject(i,Map.class);
                indicatorList.add(mp);
            }
        }
        // 空行
        indicatorList.add(new HashMap<>());
        // 校准前参数 标题
        indicatorList.add(new HashMap<String, Object>() {{
            put("temperatureRange", "校准前参数");
        }});

        String beforeCalibrationParam = req.getBeforeCalibrationParam();
        if (StrUtil.isNotBlank(beforeCalibrationParam)) {
            String[] params = beforeCalibrationParam.split("\r\n");
            for (String param : params) {
                indicatorList.add(new HashMap<String, Object>() {{
                    put("temperatureRange", param);
                }});
            }
        }
        // 空行
        indicatorList.add(new HashMap<>());
        // 本次校准参数 标题
        indicatorList.add(new HashMap<String, Object>() {{
            put("temperatureRange", "本次校准参数");
        }});

        // 本次校准参数 行
        String currentCalibrationParam = req.getCurrentCalibrationParam();
        if (StrUtil.isNotBlank(currentCalibrationParam)) {
            String[] params = currentCalibrationParam.split("\r\n");
            for (String param : params) {
                indicatorList.add(new HashMap<String, Object>() {{
                    put("temperatureRange", param);
                }});
            }
        }
        Flux<Map<String, Object>> indicatorSheetRows = Flux.fromIterable(indicatorList);

        return sheet -> {
            sheet.name("指标数据")
                 .header(new ExcelHeader("temperatureRange", "温度段(℃)", CellDataType.STRING))
                 .header(new ExcelHeader("indicationError", "示值误差(℃)", CellDataType.STRING))
                 .header(new ExcelHeader("fluctuation", "波动度(℃)", CellDataType.NUMBER))
                 .header(new ExcelHeader("uniformity", "均匀度(℃)", CellDataType.NUMBER))
                 .header(new ExcelHeader("durationError", "持续时间误差(s)", CellDataType.NUMBER))
                 .header(new ExcelHeader("heatingRate", "升温速率(℃/s)", CellDataType.NUMBER))
                 .header(new ExcelHeader("coolingRate", "降温速率(℃/s)", CellDataType.NUMBER))
                 .rows(indicatorSheetRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 6))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 6, 18));
        };
    }

    /**
     * 获取校准信息Sheet配置
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getCalibrationInfoSheet(TemperatureCalibrationExportRequest req) {
        // 解析循环步骤信息
        JSONArray stepPanels = parseStepPanels(req.getRequestBody());

        // 构建基础信息行
        List<Map<String, Object>> tableRows = buildCalibrationInfoRows(req, stepPanels);

        // 填充校准结果数据
        fillCalibrationResults(tableRows, req.getCalibrationResults());

        return sheet -> {
            sheet.name("校准信息")
                 .header(new ExcelHeader("deviceId", "整机ID", CellDataType.STRING))
                 .header(new ExcelHeader("value", req.getDeviceId(), CellDataType.STRING))
                 .header(new ExcelHeader("blankCol", "", CellDataType.STRING))
                 .header(new ExcelHeader("indicatorName", "指标名称", CellDataType.STRING))
                 .header(new ExcelHeader("testData", "测试数据", CellDataType.STRING))
                 .header(new ExcelHeader("indicator", "指标", CellDataType.STRING))
                 .header(new ExcelHeader("result", "是否合格", CellDataType.STRING))
                 .header(new ExcelHeader("gapCol", "", CellDataType.STRING))
                 .header(new ExcelHeader("type", "类型", CellDataType.STRING))
                 .header(new ExcelHeader("cycleCount", "循环次数", CellDataType.STRING))
                 .header(new ExcelHeader("isLight", "是否测光", CellDataType.STRING))
                 .header(new ExcelHeader("cycleTemp", "温度", CellDataType.STRING))
                 .header(new ExcelHeader("cycleDelay", "时间", CellDataType.STRING))
                 .header(new ExcelHeader("cycleSpeed", "速率", CellDataType.STRING))
                 .rows(Flux.fromIterable(tableRows))
                 .option(poiSheet -> applyHeaderBold(poiSheet, 3, 6))
                 .option(poiSheet -> applyHeaderBold(poiSheet, 8, 13))
                 .option(poiSheet -> applyCalibrationSheetStyling(poiSheet, stepPanels.size()));
        };
    }

    /**
     * 解析步骤面板信息
     */
    private JSONArray parseStepPanels(String requestBody) {
        if (StrUtil.isBlank(requestBody)) {
            return new JSONArray();
        }

        try {
            JSONObject requestBodyObj = JSON.parseObject(requestBody);
            JSONObject bodyObj = requestBodyObj.getJSONObject("body");
            if (bodyObj == null) return new JSONArray();

            JSONObject data = bodyObj.getJSONObject("data");
            if (data == null) return new JSONArray();

            JSONObject sequence = data.getJSONObject("sequence");
            if (sequence == null) return new JSONArray();

            String stepStr = sequence.getString("step");
            if (StrUtil.isBlank(stepStr)) return new JSONArray();

            JSONObject stepObj = JSON.parseObject(stepStr);
            JSONArray groups = stepObj.getJSONArray("groups");
            if (groups == null || groups.isEmpty()) return new JSONArray();

            return groups.getJSONObject(0).getJSONArray("panels");
        } catch (Exception e) {
            log.warn("解析步骤面板信息失败: {}", e.getMessage());
            return new JSONArray();
        }
    }

    /**
     * 构建校准信息行数据
     */
    private List<Map<String, Object>> buildCalibrationInfoRows(TemperatureCalibrationExportRequest req, JSONArray stepPanels) {
        // 定义基础信息配置
        List<BasicInfoConfig> basicInfoConfigs = Arrays.asList(
            new BasicInfoConfig("模块编号", req.getModuleCode()),
            new BasicInfoConfig("校准日期", req.getCalibrationTime()),
            new BasicInfoConfig("校准人", req.getCalibrationUser()),
            new BasicInfoConfig("备注", req.getRemark())
        );

        // 定义指标配置
        List<String> indicatorNames = Arrays.asList(
            "示值误差(℃)", "波动度(℃)", "均匀度(℃)",
            "持续时间误差(s)", "升温速率(℃/s)", "降温速率(℃/s)"
        );

        List<Map<String, Object>> rows = new ArrayList<>();

        // 构建行数据
        for (int i = 0; i < Math.max(basicInfoConfigs.size(), indicatorNames.size()); i++) {
            Map<String, Object> row = new HashMap<>();

            // 基础信息列 (A-B)
            if (i < basicInfoConfigs.size()) {
                BasicInfoConfig config = basicInfoConfigs.get(i);
                row.put("deviceId", config.label);
                row.put("value", config.value);
            }

            // 空白列 (C)
            row.put("blankCol", "");

            // 指标列 (D-G)
            if (i < indicatorNames.size()) {
                row.put("indicatorName", indicatorNames.get(i));
                row.put("testData", "");
                row.put("indicator", "");
                row.put("result", "");
            }

            // 间隔列 (H)
            row.put("gapCol", "");

            // 循环信息列 (I-N)
            if (i == 0) {
                row.put("type", "循环段");
                row.put("cycleCount", "-");
            }

            // 填充循环步骤信息
            if (stepPanels != null && i < stepPanels.size()) {
                JSONObject panel = stepPanels.getJSONObject(i);
                row.put("isLight", panel.getBooleanValue("testlight") ? "是" : "否");
                row.put("cycleTemp", panel.getString("temperature"));
                row.put("cycleDelay", panel.getString("delay"));
                row.put("cycleSpeed", panel.getString("speed"));
            } else {
                row.put("isLight", "");
                row.put("cycleTemp", "");
                row.put("cycleDelay", "");
                row.put("cycleSpeed", "");
            }

            rows.add(row);
        }

        return rows;
    }

    /**
     * 填充校准结果数据
     */
    private void fillCalibrationResults(List<Map<String, Object>> rows, List<CalibrationResult> results) {
        if (CollectionUtil.isEmpty(results)) {
            return;
        }

        // 创建结果映射
        Map<String, CalibrationResult> resultMap = results.stream()
            .collect(Collectors.toMap(
                CalibrationResult::getIndicatorName,
                result -> result,
                (existing, replacement) -> replacement
            ));

        // 填充结果数据
        rows.forEach(row -> {
            String indicatorName = (String) row.get("indicatorName");
            if (StrUtil.isNotBlank(indicatorName)) {
                CalibrationResult result = resultMap.get(indicatorName);
                if (result != null) {
                    row.put("testData", result.getTestData());
                    row.put("indicator", result.getIndicator());
                    row.put("result", result.getResult());
                }
            }
        });
    }

    /**
     * 基础信息配置类
     */
    private static class BasicInfoConfig {
        final String label;
        final String value;

        BasicInfoConfig(String label, String value) {
            this.label = label;
            this.value = value != null ? value : "";
        }
    }

    /**
     * 应用校准Sheet样式
     */
    private void applyCalibrationSheetStyling(Sheet sheetObj, int cycleDetailCount) {
        Row headerRow = sheetObj.getRow(0);
        Workbook wb = sheetObj.getWorkbook();
        Font boldFont = wb.createFont();
        boldFont.setBold(true);
        CellStyle boldStyle = wb.createCellStyle();
        boldStyle.setFont(boldFont);

        // 第二部分（指标）列索引：3-6；第三部分（循环）列索引：8-13
        int[] boldCols = new int[]{3, 4, 5, 6, 8, 9, 10, 11, 12, 13};
        if (headerRow == null) {
            headerRow = sheetObj.createRow(0);
        }
        for (int colIndex : boldCols) {
            Cell cell = headerRow.getCell(colIndex);
            if (cell == null) cell = headerRow.createCell(colIndex);
            cell.setCellStyle(boldStyle);
        }

        // 合并第三部分的"类型"列（索引8）和"循环次数"列（索引9）
        if (cycleDetailCount > 0) {
            sheetObj.addMergedRegion(new CellRangeAddress(1, cycleDetailCount, 8, 8));
            sheetObj.addMergedRegion(new CellRangeAddress(1, cycleDetailCount, 9, 9));
        }
    }

    /**
     * 通用：加粗首行表头 0..lastColIndex 列
     */
    private void applyHeaderBold(Sheet sheetObj, int lastColIndex) {
        Row headerRow = sheetObj.getRow(0);
        if (headerRow == null) {
            headerRow = sheetObj.createRow(0);
        }
        Workbook wb = sheetObj.getWorkbook();
        Font boldFont = wb.createFont();
        boldFont.setBold(true);
        CellStyle boldStyle = wb.createCellStyle();
        boldStyle.setFont(boldFont);
        for (int colIndex = 0; colIndex <= lastColIndex; colIndex++) {
            Cell cell = headerRow.getCell(colIndex);
            if (cell == null) cell = headerRow.createCell(colIndex);
            cell.setCellStyle(boldStyle);
        }
    }
    private void applyHeaderBold(Sheet sheetObj,int startColIndex, int lastColIndex) {
        Row headerRow = sheetObj.getRow(0);
        if (headerRow == null) {
            headerRow = sheetObj.createRow(0);
        }
        Workbook wb = sheetObj.getWorkbook();
        Font boldFont = wb.createFont();
        boldFont.setBold(true);
        CellStyle boldStyle = wb.createCellStyle();
        boldStyle.setFont(boldFont);
        for (int colIndex = startColIndex; colIndex <= lastColIndex; colIndex++) {
            Cell cell = headerRow.getCell(colIndex);
            if (cell == null) cell = headerRow.createCell(colIndex);
            cell.setCellStyle(boldStyle);
        }
    }
    /**
     * 通用：设置列宽，0..lastColIndex 列统一宽度（单位：字符数）
     */
    private void applyColumnWidths(Sheet sheetObj, int lastColIndex, int widthChars) {
        int width = widthChars * 256;
        for (int i = 0; i <= lastColIndex; i++) {
            sheetObj.setColumnWidth(i, width);
        }
    }
    private void applyColumnWidths(Sheet sheetObj,int startColIndex, int lastColIndex, int widthChars) {
        int width = widthChars * 256;
        for (int i = startColIndex; i <= lastColIndex; i++) {
            sheetObj.setColumnWidth(i, width);
        }
    }
    /**
     * 通用：加粗"参数"表头行
     */
    private void applyParamHeaderBold(Sheet sheetObj) {
        try {
            Workbook wb = sheetObj.getWorkbook();
            Font boldFont = wb.createFont();
            boldFont.setBold(true);
            CellStyle boldStyle = wb.createCellStyle();
            boldStyle.setFont(boldFont);

            // 查找包含"参数"的行并加粗
            for (int rowIndex = 0; rowIndex <= sheetObj.getLastRowNum(); rowIndex++) {
                Row row = sheetObj.getRow(rowIndex);
                if (row != null) {
                    Cell cell = row.getCell(0); // 第一列（浓度列）
                    if (cell != null) {
                        String cellValue = "";
                        try {
                            cellValue = cell.getStringCellValue();
                        } catch (Exception e) {
                            // 如果不是字符串类型，尝试其他方式获取值
                            try {
                                cellValue = String.valueOf(cell.getNumericCellValue());
                            } catch (Exception ex) {
                                // 忽略异常，继续处理
                            }
                        }

                        if ("参数".equals(cellValue)) {
                            // 加粗整行
                            for (int colIndex = 0; colIndex < 5; colIndex++) {
                                Cell cellToBold = row.getCell(colIndex);
                                if (cellToBold == null) {
                                    cellToBold = row.createCell(colIndex);
                                }
                                cellToBold.setCellStyle(boldStyle);
                            }
                            break;
                        }
                    }
                }
            }
        } catch (Exception e) {
            // 如果样式设置失败，记录日志但不影响整体功能
            log.warn("设置参数表头加粗样式失败: {}", e.getMessage());
        }
    }

    /**
     * 延迟执行参数表头加粗样式设置
     */
    private void applyParamHeaderBoldDelayed(Sheet sheetObj) {
        // 使用延迟执行，确保数据已经写入
        new Thread(() -> {
            try {
                Thread.sleep(100); // 等待100毫秒确保数据写入完成
                applyParamHeaderBold(sheetObj);
            } catch (Exception e) {
                log.warn("延迟执行参数表头加粗样式失败: {}", e.getMessage());
            }
        }).start();
    }


    /**
     * 荧光校准单模组-校准结果
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getCalibrationResultsSheetSingle(FluorescenceCalibrationDataExportSingleRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {

            Map<String, Object> sd = new HashMap<>();
            sd.put("project", "标准差<0.02");
            sd.put("fam", r.getMaxSD().get(0));
            sd.put("vic", r.getMaxSD().get(1));
            sd.put("rox", r.getMaxSD().get(2));
            sd.put("cy5", r.getMaxSD().get(3));
            fluxSink.next(sd);
            Map<String, Object> k = new HashMap<>();
            k.put("project", "k");
            k.put("fam", r.getB().get(0).get(0));
            k.put("vic", r.getB().get(1).get(0));
            k.put("rox", r.getB().get(2).get(0));
            k.put("cy5", r.getB().get(3).get(0));
            fluxSink.next(k);
            Map<String, Object> b = new HashMap<>();
            b.put("project", "b");
            b.put("fam", r.getB().get(0).get(1));
            b.put("vic", r.getB().get(1).get(1));
            b.put("rox", r.getB().get(2).get(1));
            b.put("cy5", r.getB().get(3).get(1));
            fluxSink.next(b);
            Map<String, Object> rSquares = new HashMap<>();
            rSquares.put("project", "R^2");
            rSquares.put("fam", r.getRsquares().get(0));
            rSquares.put("vic", r.getRsquares().get(1));
            rSquares.put("rox", r.getRsquares().get(2));
            rSquares.put("cy5", r.getRsquares().get(3));
            fluxSink.next(rSquares);
            Map<String, Object> snr = new HashMap<>();
            snr.put("project", "信噪比>120");
            snr.put("fam", r.getSnr().get(0));
            snr.put("vic", r.getSnr().get(1));
            snr.put("rox", r.getSnr().get(2));
            snr.put("cy5", r.getSnr().get(3));
            fluxSink.next(snr);
            Map<String, Object> isQualified = new HashMap<>();
            isQualified.put("project", "是否合格");
            isQualified.put("fam", r.getCheckResult().get(0));
            isQualified.put("vic", r.getCheckResult().get(1));
            isQualified.put("rox", r.getCheckResult().get(2));
            isQualified.put("cy5", r.getCheckResult().get(3));
            fluxSink.next(isQualified);

            // 空行
            fluxSink.next(new HashMap<>());
            fluxSink.next(new HashMap<>());
            fluxSink.next(new HashMap<>());

            StringBuilder sb = new StringBuilder();
            sb.append("(").append(r.getB().get(0).get(0)).append(",").append(r.getB().get(0).get(1)).append(")")
              .append("(").append(r.getB().get(1).get(0)).append(",").append(r.getB().get(1).get(1)).append(")")
              .append("(").append(r.getB().get(2).get(0)).append(",").append(r.getB().get(2).get(1)).append(")")
              .append("(").append(r.getB().get(3).get(0)).append(",").append(r.getB().get(3).get(1)).append(")");


            Map<String, Object> currentCalibrationData = new HashMap<>();
            currentCalibrationData.put("project", "本次校准参数");
            currentCalibrationData.put("fam", sb.toString());
            fluxSink.next(currentCalibrationData);

            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("校准结果")
                 .header(new ExcelHeader("project", "项目", CellDataType.STRING))
                 .header(new ExcelHeader("fam", "FAM", CellDataType.NUMBER))
                 .header(new ExcelHeader("vic", "VIC", CellDataType.NUMBER))
                 .header(new ExcelHeader("rox", "ROX", CellDataType.NUMBER))
                 .header(new ExcelHeader("cy5", "CY5", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4));
        };
    }

    /**
     * 荧光校准单模组-原始数据
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getRawDataSheetSingle(FluorescenceCalibrationDataExportSingleRequest req) {

        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {
            Map<String, Object> density100 = new HashMap<>();
            density100.put("density", "100");
            density100.put("fam", r.getMeanStats().get(0).get(0));
            density100.put("vic", r.getMeanStats().get(0).get(1));
            density100.put("rox", r.getMeanStats().get(0).get(2));
            density100.put("cy5", r.getMeanStats().get(0).get(3));
            fluxSink.next(density100);

            Map<String, Object> density200 = new HashMap<>();
            density200.put("density", "200");
            density200.put("fam", r.getMeanStats().get(1).get(0));
            density200.put("vic", r.getMeanStats().get(1).get(1));
            density200.put("rox", r.getMeanStats().get(1).get(2));
            density200.put("cy5", r.getMeanStats().get(1).get(3));
            fluxSink.next(density200);

            Map<String, Object> density400 = new HashMap<>();
            density400.put("density", "400");
            density400.put("fam", r.getMeanStats().get(2).get(0));
            density400.put("vic", r.getMeanStats().get(2).get(1));
            density400.put("rox", r.getMeanStats().get(2).get(2));
            density400.put("cy5", r.getMeanStats().get(2).get(3));
            fluxSink.next(density400);

            Map<String, Object> density800 = new HashMap<>();
            density800.put("density", "800");
            density800.put("fam", r.getMeanStats().get(3).get(0));
            density800.put("vic", r.getMeanStats().get(3).get(1));
            density800.put("rox", r.getMeanStats().get(3).get(2));
            density800.put("cy5", r.getMeanStats().get(3).get(3));
            fluxSink.next(density800);

            Map<String, Object> density1600 = new HashMap<>();
            density1600.put("density", "1600");
            density1600.put("fam", r.getMeanStats().get(4).get(0));
            density1600.put("vic", r.getMeanStats().get(4).get(1));
            density1600.put("rox", r.getMeanStats().get(4).get(2));
            density1600.put("cy5", r.getMeanStats().get(4).get(3));
            fluxSink.next(density1600);

            fluxSink.complete();
        }));


        return sheet -> {
            sheet.name("原始数据")
                 .header(new ExcelHeader("density", "浓度", CellDataType.STRING))
                 .header(new ExcelHeader("fam", "FAM", CellDataType.NUMBER))
                 .header(new ExcelHeader("vic", "VIC", CellDataType.NUMBER))
                 .header(new ExcelHeader("rox", "ROX", CellDataType.NUMBER))
                 .header(new ExcelHeader("cy5", "CY5", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18));
        };
    }

    /**
     * 荧光校准单模组-校准后数据
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getCalibrationAfterDataSheetSingle(FluorescenceCalibrationDataExportSingleRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {

            Map<String, Object> density100 = new HashMap<>();
            density100.put("density", "100");
            density100.put("fam", r.getD0_FAM().get(0));
            density100.put("vic", r.getD0_VIC().get(0));
            density100.put("rox", r.getD0_ROX().get(0));
            density100.put("cy5", r.getD0_Cy5().get(0));
            fluxSink.next(density100);

            Map<String, Object> density200 = new HashMap<>();
            density200.put("density", "200");
            density200.put("fam", r.getD0_FAM().get(1));
            density200.put("vic", r.getD0_VIC().get(1));
            density200.put("rox", r.getD0_ROX().get(1));
            density200.put("cy5", r.getD0_Cy5().get(1));
            fluxSink.next(density200);

            Map<String, Object> density400 = new HashMap<>();
            density400.put("density", "400");
            density400.put("fam", r.getD0_FAM().get(2));
            density400.put("vic", r.getD0_VIC().get(2));
            density400.put("rox", r.getD0_ROX().get(2));
            density400.put("cy5", r.getD0_Cy5().get(2));
            fluxSink.next(density400);

            Map<String, Object> density800 = new HashMap<>();
            density800.put("density", "800");
            density800.put("fam", r.getD0_FAM().get(3));
            density800.put("vic", r.getD0_VIC().get(3));
            density800.put("rox", r.getD0_ROX().get(3));
            density800.put("cy5", r.getD0_Cy5().get(3));
            fluxSink.next(density800);

            Map<String, Object> density1600 = new HashMap<>();
            density1600.put("density", "1600");
            density1600.put("fam", r.getD0_FAM().get(4));
            density1600.put("vic", r.getD0_VIC().get(4));
            density1600.put("rox", r.getD0_ROX().get(4));
            density1600.put("cy5", r.getD0_Cy5().get(4));
            fluxSink.next(density1600);

            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("校准后数据")
                 .header(new ExcelHeader("density", "浓度", CellDataType.STRING))
                 .header(new ExcelHeader("fam", "FAM", CellDataType.NUMBER))
                 .header(new ExcelHeader("vic", "VIC", CellDataType.NUMBER))
                 .header(new ExcelHeader("rox", "ROX", CellDataType.NUMBER))
                 .header(new ExcelHeader("cy5", "CY5", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18));
        };
    }

    /**
     * 荧光校准单模组-FAM
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getFAMDataSheetSingle(FluorescenceCalibrationDataExportSingleRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {
            List<List<Double>> famSelected = r.getFamSelected();
            if (famSelected != null && famSelected.size() >= 5) {
                int maxRows = 0;
                for (List<Double> list : famSelected) {
                    maxRows = Math.max(maxRows, list == null ? 0 : list.size());
                }
                for (int row = 0; row < maxRows; row++) {
                    Map<String, Object> m = new HashMap<>();
                    m.put("100", (famSelected.get(0) != null && famSelected.get(0).size() > row) ? famSelected
                        .get(0)
                        .get(row) : null);
                    m.put("200", (famSelected.get(1) != null && famSelected.get(1).size() > row) ? famSelected
                        .get(1)
                        .get(row) : null);
                    m.put("400", (famSelected.get(2) != null && famSelected.get(2).size() > row) ? famSelected
                        .get(2)
                        .get(row) : null);
                    m.put("800", (famSelected.get(3) != null && famSelected.get(3).size() > row) ? famSelected
                        .get(3)
                        .get(row) : null);
                    m.put("1600", (famSelected.get(4) != null && famSelected.get(4).size() > row) ? famSelected
                        .get(4)
                        .get(row) : null);
                    fluxSink.next(m);
                }
            }
            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("FAM")
                 .header(new ExcelHeader("100", "100nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("200", "200nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("400", "400nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("800", "800nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("1600", "1600nM", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18));
        };
    }

    /**
     * 荧光校准单模组-VIC
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getVICDataSheetSingle(FluorescenceCalibrationDataExportSingleRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {
            List<List<Double>> vicSelected = r.getVicSelected();
            if (vicSelected != null && vicSelected.size() >= 5) {
                int maxRows = 0;
                for (List<Double> list : vicSelected) {
                    maxRows = Math.max(maxRows, list == null ? 0 : list.size());
                }
                for (int row = 0; row < maxRows; row++) {
                    Map<String, Object> m = new HashMap<>();
                    m.put("100", (vicSelected.get(0) != null && vicSelected.get(0).size() > row) ? vicSelected
                        .get(0)
                        .get(row) : null);
                    m.put("200", (vicSelected.get(1) != null && vicSelected.get(1).size() > row) ? vicSelected
                        .get(1)
                        .get(row) : null);
                    m.put("400", (vicSelected.get(2) != null && vicSelected.get(2).size() > row) ? vicSelected
                        .get(2)
                        .get(row) : null);
                    m.put("800", (vicSelected.get(3) != null && vicSelected.get(3).size() > row) ? vicSelected
                        .get(3)
                        .get(row) : null);
                    m.put("1600", (vicSelected.get(4) != null && vicSelected.get(4).size() > row) ? vicSelected
                        .get(4)
                        .get(row) : null);
                    fluxSink.next(m);
                }
            }
            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("VIC")
                 .header(new ExcelHeader("100", "100nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("200", "200nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("400", "400nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("800", "800nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("1600", "1600nM", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18));
        };
    }

    /**
     * 荧光校准单模组-ROX
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getROXDataSheetSingle(FluorescenceCalibrationDataExportSingleRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {
            List<List<Double>> roxSelected = r.getRoxSelected();
            if (roxSelected != null && roxSelected.size() >= 5) {
                int maxRows = 0;
                for (List<Double> list : roxSelected) {
                    maxRows = Math.max(maxRows, list == null ? 0 : list.size());
                }
                for (int row = 0; row < maxRows; row++) {
                    Map<String, Object> m = new HashMap<>();
                    m.put("100", (roxSelected.get(0) != null && roxSelected.get(0).size() > row) ? roxSelected
                        .get(0)
                        .get(row) : null);
                    m.put("200", (roxSelected.get(1) != null && roxSelected.get(1).size() > row) ? roxSelected
                        .get(1)
                        .get(row) : null);
                    m.put("400", (roxSelected.get(2) != null && roxSelected.get(2).size() > row) ? roxSelected
                        .get(2)
                        .get(row) : null);
                    m.put("800", (roxSelected.get(3) != null && roxSelected.get(3).size() > row) ? roxSelected
                        .get(3)
                        .get(row) : null);
                    m.put("1600", (roxSelected.get(4) != null && roxSelected.get(4).size() > row) ? roxSelected
                        .get(4)
                        .get(row) : null);
                    fluxSink.next(m);
                }
            }
            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("ROX")
                 .header(new ExcelHeader("100", "100nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("200", "200nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("400", "400nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("800", "800nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("1600", "1600nM", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18));
        };
    }

    /**
     * 荧光校准单模组-Cy5
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getCy5DataSheetSingle(FluorescenceCalibrationDataExportSingleRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {
            List<List<Double>> cy5Selected = r.getCy5Selected();
            if (cy5Selected != null && cy5Selected.size() >= 5) {
                int maxRows = 0;
                for (List<Double> list : cy5Selected) {
                    maxRows = Math.max(maxRows, list == null ? 0 : list.size());
                }
                for (int row = 0; row < maxRows; row++) {
                    Map<String, Object> m = new HashMap<>();
                    m.put("100", (cy5Selected.get(0) != null && cy5Selected.get(0).size() > row) ? cy5Selected
                        .get(0)
                        .get(row) : null);
                    m.put("200", (cy5Selected.get(1) != null && cy5Selected.get(1).size() > row) ? cy5Selected
                        .get(1)
                        .get(row) : null);
                    m.put("400", (cy5Selected.get(2) != null && cy5Selected.get(2).size() > row) ? cy5Selected
                        .get(2)
                        .get(row) : null);
                    m.put("800", (cy5Selected.get(3) != null && cy5Selected.get(3).size() > row) ? cy5Selected
                        .get(3)
                        .get(row) : null);
                    m.put("1600", (cy5Selected.get(4) != null && cy5Selected.get(4).size() > row) ? cy5Selected
                        .get(4)
                        .get(row) : null);
                    fluxSink.next(m);
                }
            }
            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("CY5")
                 .header(new ExcelHeader("100", "100nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("200", "200nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("400", "400nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("800", "800nM", CellDataType.NUMBER))
                 .header(new ExcelHeader("1600", "1600nM", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18));
        };
    }

    /**
     * 荧光校准整机-整机
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getWholeMachineDataSheet(FluorescenceCalibrationDataExportWholeRequest req, String currentCalibrationParam, String beforeCalibrationParam) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {

            Map<String, Object> density100 = new HashMap<>();
            density100.put("density", "100");
            density100.put("fam", r.getRsdMatrix().get(0).get(0));
            density100.put("vic", r.getRsdMatrix().get(0).get(1));
            density100.put("rox", r.getRsdMatrix().get(0).get(2));
            density100.put("cy5", r.getRsdMatrix().get(0).get(3));
            fluxSink.next(density100);

            Map<String, Object> density200 = new HashMap<>();
            density200.put("density", "200");
            density200.put("fam", r.getRsdMatrix().get(1).get(0));
            density200.put("vic", r.getRsdMatrix().get(1).get(1));
            density200.put("rox", r.getRsdMatrix().get(1).get(2));
            density200.put("cy5", r.getRsdMatrix().get(1).get(3));
            fluxSink.next(density200);

            Map<String, Object> density400 = new HashMap<>();
            density400.put("density", "400");
            density400.put("fam", r.getRsdMatrix().get(2).get(0));
            density400.put("vic", r.getRsdMatrix().get(2).get(1));
            density400.put("rox", r.getRsdMatrix().get(2).get(2));
            density400.put("cy5", r.getRsdMatrix().get(2).get(3));
            fluxSink.next(density400);

            Map<String, Object> density800 = new HashMap<>();
            density800.put("density", "800");
            density800.put("fam", r.getRsdMatrix().get(3).get(0));
            density800.put("vic", r.getRsdMatrix().get(3).get(1));
            density800.put("rox", r.getRsdMatrix().get(3).get(2));
            density800.put("cy5", r.getRsdMatrix().get(3).get(3));
            fluxSink.next(density800);

            Map<String, Object> density1600 = new HashMap<>();
            density1600.put("density", "1600");
            density1600.put("fam", r.getRsdMatrix().get(4).get(0));
            density1600.put("vic", r.getRsdMatrix().get(4).get(1));
            density1600.put("rox", r.getRsdMatrix().get(4).get(2));
            density1600.put("cy5", r.getRsdMatrix().get(4).get(3));
            fluxSink.next(density1600);


            // 空行
            fluxSink.next(new HashMap<>());
            fluxSink.next(new HashMap<>());
            fluxSink.next(new HashMap<>());
            Map<String, Object> beforeCalibrationData = new HashMap<>();
            beforeCalibrationData.put("density", "校准前参数");
            beforeCalibrationData.put("fam", beforeCalibrationParam);
            fluxSink.next(beforeCalibrationData);
            fluxSink.next(new HashMap<>());
            fluxSink.next(new HashMap<>());
            fluxSink.next(new HashMap<>());
            fluxSink.next(new HashMap<>());
            Map<String, Object> currentCalibrationData = new HashMap<>();
            currentCalibrationData.put("density", "本次校准参数");
            currentCalibrationData.put("fam", currentCalibrationParam);
            fluxSink.next(currentCalibrationData);


            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("整机")
                 .header(new ExcelHeader("density", "浓度", CellDataType.STRING))
                 .header(new ExcelHeader("fam", "FAM", CellDataType.NUMBER))
                 .header(new ExcelHeader("vic", "VIC", CellDataType.NUMBER))
                 .header(new ExcelHeader("rox", "ROX", CellDataType.NUMBER))
                 .header(new ExcelHeader("cy5", "CY5", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18));
        };
    }

    /**
     * 荧光校准整机-Adjusted1
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getAdjusted1DataSheet(FluorescenceCalibrationDataExportWholeRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {
            // 获取第一个模组的数据
            if (r.getModules() != null && r.getModules().size() > 0) {
                Module module1 = r.getModules().get(0);

                // 添加浓度数据行（100, 200, 400, 800, 1600）
                if (module1.getAdjustedData() != null && module1.getAdjustedData().size() >= 5) {
                    String[] concentrations = {"100", "200", "400", "800", "1600"};
                    for (int i = 0; i < 5; i++) {
                        Map<String, Object> row = new HashMap<>();
                        row.put("density", concentrations[i]);
                        if (module1.getAdjustedData().get(i) != null && module1.getAdjustedData().get(i).size() >= 4) {
                            row.put("fam", module1.getAdjustedData().get(i).get(0));
                            row.put("vic", module1.getAdjustedData().get(i).get(1));
                            row.put("rox", module1.getAdjustedData().get(i).get(2));
                            row.put("cy5", module1.getAdjustedData().get(i).get(3));
                        }
                        fluxSink.next(row);
                    }
                }

                // 添加空行
                fluxSink.next(new HashMap<>());
                fluxSink.next(new HashMap<>());
                fluxSink.next(new HashMap<>());

                // 添加参数表头行
                Map<String, Object> paramHeaderRow = new HashMap<>();
                paramHeaderRow.put("density", "参数");
                paramHeaderRow.put("fam", "FAM");
                paramHeaderRow.put("vic", "VIC");
                paramHeaderRow.put("rox", "ROX");
                paramHeaderRow.put("cy5", "Cy5");
                paramHeaderRow.put("_isParamHeader", true); // 标记这是参数表头行
                fluxSink.next(paramHeaderRow);

                // 添加参数k行
                Map<String, Object> kRow = new HashMap<>();
                kRow.put("density", "k");
                if (module1.getK() != null && module1.getK().size() >= 4) {
                    kRow.put("fam", module1.getK().get(0));
                    kRow.put("vic", module1.getK().get(1));
                    kRow.put("rox", module1.getK().get(2));
                    kRow.put("cy5", module1.getK().get(3));
                }
                fluxSink.next(kRow);

                // 添加参数b行
                Map<String, Object> bRow = new HashMap<>();
                bRow.put("density", "b");
                if (module1.getB() != null && module1.getB().size() >= 4) {
                    bRow.put("fam", module1.getB().get(0));
                    bRow.put("vic", module1.getB().get(1));
                    bRow.put("rox", module1.getB().get(2));
                    bRow.put("cy5", module1.getB().get(3));
                }
                fluxSink.next(bRow);
            }

            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("Adjusted1")
                 .header(new ExcelHeader("density", "浓度", CellDataType.STRING))
                 .header(new ExcelHeader("fam", "FAM", CellDataType.NUMBER))
                 .header(new ExcelHeader("vic", "VIC", CellDataType.NUMBER))
                 .header(new ExcelHeader("rox", "ROX", CellDataType.NUMBER))
                 .header(new ExcelHeader("cy5", "Cy5", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18))
                 .option(poiSheet -> applyParamHeaderBoldDelayed(poiSheet));
        };
    }

    /**
     * 荧光校准整机-Adjusted2
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getAdjusted2DataSheet(FluorescenceCalibrationDataExportWholeRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {
            // 获取第二个模组的数据
            if (r.getModules() != null && r.getModules().size() > 1) {
                Module module2 = r.getModules().get(1);

                // 添加浓度数据行（100, 200, 400, 800, 1600）
                if (module2.getAdjustedData() != null && module2.getAdjustedData().size() >= 5) {
                    String[] concentrations = {"100", "200", "400", "800", "1600"};
                    for (int i = 0; i < 5; i++) {
                        Map<String, Object> row = new HashMap<>();
                        row.put("density", concentrations[i]);
                        if (module2.getAdjustedData().get(i) != null && module2.getAdjustedData().get(i).size() >= 4) {
                            row.put("fam", module2.getAdjustedData().get(i).get(0));
                            row.put("vic", module2.getAdjustedData().get(i).get(1));
                            row.put("rox", module2.getAdjustedData().get(i).get(2));
                            row.put("cy5", module2.getAdjustedData().get(i).get(3));
                        }
                        fluxSink.next(row);
                    }
                }

                // 添加空行
                fluxSink.next(new HashMap<>());
                fluxSink.next(new HashMap<>());
                fluxSink.next(new HashMap<>());

                // 添加参数表头行
                Map<String, Object> paramHeaderRow = new HashMap<>();
                paramHeaderRow.put("density", "参数");
                paramHeaderRow.put("fam", "FAM");
                paramHeaderRow.put("vic", "VIC");
                paramHeaderRow.put("rox", "ROX");
                paramHeaderRow.put("cy5", "Cy5");
                paramHeaderRow.put("_isParamHeader", true); // 标记这是参数表头行
                fluxSink.next(paramHeaderRow);

                // 添加参数k行
                Map<String, Object> kRow = new HashMap<>();
                kRow.put("density", "k");
                if (module2.getK() != null && module2.getK().size() >= 4) {
                    kRow.put("fam", module2.getK().get(0));
                    kRow.put("vic", module2.getK().get(1));
                    kRow.put("rox", module2.getK().get(2));
                    kRow.put("cy5", module2.getK().get(3));
                }
                fluxSink.next(kRow);

                // 添加参数b行
                Map<String, Object> bRow = new HashMap<>();
                bRow.put("density", "b");
                if (module2.getB() != null && module2.getB().size() >= 4) {
                    bRow.put("fam", module2.getB().get(0));
                    bRow.put("vic", module2.getB().get(1));
                    bRow.put("rox", module2.getB().get(2));
                    bRow.put("cy5", module2.getB().get(3));
                }
                fluxSink.next(bRow);
            }

            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("Adjusted2")
                 .header(new ExcelHeader("density", "浓度", CellDataType.STRING))
                 .header(new ExcelHeader("fam", "FAM", CellDataType.NUMBER))
                 .header(new ExcelHeader("vic", "VIC", CellDataType.NUMBER))
                 .header(new ExcelHeader("rox", "ROX", CellDataType.NUMBER))
                 .header(new ExcelHeader("cy5", "Cy5", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18))
                 .option(poiSheet -> applyParamHeaderBoldDelayed(poiSheet));
        };
    }

    /**
     * 荧光校准整机-Adjusted3
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getAdjusted3DataSheet(FluorescenceCalibrationDataExportWholeRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {
            // 获取第三个模组的数据
            if (r.getModules() != null && r.getModules().size() > 2) {
                Module module3 = r.getModules().get(2);

                // 添加浓度数据行（100, 200, 400, 800, 1600）
                if (module3.getAdjustedData() != null && module3.getAdjustedData().size() >= 5) {
                    String[] concentrations = {"100", "200", "400", "800", "1600"};
                    for (int i = 0; i < 5; i++) {
                        Map<String, Object> row = new HashMap<>();
                        row.put("density", concentrations[i]);
                        if (module3.getAdjustedData().get(i) != null && module3.getAdjustedData().get(i).size() >= 4) {
                            row.put("fam", module3.getAdjustedData().get(i).get(0));
                            row.put("vic", module3.getAdjustedData().get(i).get(1));
                            row.put("rox", module3.getAdjustedData().get(i).get(2));
                            row.put("cy5", module3.getAdjustedData().get(i).get(3));
                        }
                        fluxSink.next(row);
                    }
                }

                // 添加空行
                fluxSink.next(new HashMap<>());
                fluxSink.next(new HashMap<>());
                fluxSink.next(new HashMap<>());

                // 添加参数表头行
                Map<String, Object> paramHeaderRow = new HashMap<>();
                paramHeaderRow.put("density", "参数");
                paramHeaderRow.put("fam", "FAM");
                paramHeaderRow.put("vic", "VIC");
                paramHeaderRow.put("rox", "ROX");
                paramHeaderRow.put("cy5", "Cy5");
                paramHeaderRow.put("_isParamHeader", true); // 标记这是参数表头行
                fluxSink.next(paramHeaderRow);

                // 添加参数k行
                Map<String, Object> kRow = new HashMap<>();
                kRow.put("density", "k");
                if (module3.getK() != null && module3.getK().size() >= 4) {
                    kRow.put("fam", module3.getK().get(0));
                    kRow.put("vic", module3.getK().get(1));
                    kRow.put("rox", module3.getK().get(2));
                    kRow.put("cy5", module3.getK().get(3));
                }
                fluxSink.next(kRow);

                // 添加参数b行
                Map<String, Object> bRow = new HashMap<>();
                bRow.put("density", "b");
                if (module3.getB() != null && module3.getB().size() >= 4) {
                    bRow.put("fam", module3.getB().get(0));
                    bRow.put("vic", module3.getB().get(1));
                    bRow.put("rox", module3.getB().get(2));
                    bRow.put("cy5", module3.getB().get(3));
                }
                fluxSink.next(bRow);
            }

            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("Adjusted3")
                 .header(new ExcelHeader("density", "浓度", CellDataType.STRING))
                 .header(new ExcelHeader("fam", "FAM", CellDataType.NUMBER))
                 .header(new ExcelHeader("vic", "VIC", CellDataType.NUMBER))
                 .header(new ExcelHeader("rox", "ROX", CellDataType.NUMBER))
                 .header(new ExcelHeader("cy5", "Cy5", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18))
                 .option(poiSheet -> applyParamHeaderBoldDelayed(poiSheet));
        };
    }

    /**
     * 荧光校准整机-Adjusted4
     */
    private Consumer<SheetSpec.HeaderSheetSpec> getAdjusted4DataSheet(FluorescenceCalibrationDataExportWholeRequest req) {
        Flux<Map<String, Object>> detailRows = Flux.just(req).flatMap(r -> Flux.create(fluxSink -> {
            // 获取第四个模组的数据
            if (r.getModules() != null && r.getModules().size() > 3) {
                Module module4 = r.getModules().get(3);

                // 添加浓度数据行（100, 200, 400, 800, 1600）
                if (module4.getAdjustedData() != null && module4.getAdjustedData().size() >= 5) {
                    String[] concentrations = {"100", "200", "400", "800", "1600"};
                    for (int i = 0; i < 5; i++) {
                        Map<String, Object> row = new HashMap<>();
                        row.put("density", concentrations[i]);
                        if (module4.getAdjustedData().get(i) != null && module4.getAdjustedData().get(i).size() >= 4) {
                            row.put("fam", module4.getAdjustedData().get(i).get(0));
                            row.put("vic", module4.getAdjustedData().get(i).get(1));
                            row.put("rox", module4.getAdjustedData().get(i).get(2));
                            row.put("cy5", module4.getAdjustedData().get(i).get(3));
                        }
                        fluxSink.next(row);
                    }
                }

                // 添加空行
                fluxSink.next(new HashMap<>());
                fluxSink.next(new HashMap<>());
                fluxSink.next(new HashMap<>());

                // 添加参数表头行
                Map<String, Object> paramHeaderRow = new HashMap<>();
                paramHeaderRow.put("density", "参数");
                paramHeaderRow.put("fam", "FAM");
                paramHeaderRow.put("vic", "VIC");
                paramHeaderRow.put("rox", "ROX");
                paramHeaderRow.put("cy5", "Cy5");
                paramHeaderRow.put("_isParamHeader", true); // 标记这是参数表头行
                fluxSink.next(paramHeaderRow);

                // 添加参数k行
                Map<String, Object> kRow = new HashMap<>();
                kRow.put("density", "k");
                if (module4.getK() != null && module4.getK().size() >= 4) {
                    kRow.put("fam", module4.getK().get(0));
                    kRow.put("vic", module4.getK().get(1));
                    kRow.put("rox", module4.getK().get(2));
                    kRow.put("cy5", module4.getK().get(3));
                }
                fluxSink.next(kRow);

                // 添加参数b行
                Map<String, Object> bRow = new HashMap<>();
                bRow.put("density", "b");
                if (module4.getB() != null && module4.getB().size() >= 4) {
                    bRow.put("fam", module4.getB().get(0));
                    bRow.put("vic", module4.getB().get(1));
                    bRow.put("rox", module4.getB().get(2));
                    bRow.put("cy5", module4.getB().get(3));
                }
                fluxSink.next(bRow);
            }

            fluxSink.complete();
        }));

        return sheet -> {
            sheet.name("Adjusted4")
                 .header(new ExcelHeader("density", "浓度", CellDataType.STRING))
                 .header(new ExcelHeader("fam", "FAM", CellDataType.NUMBER))
                 .header(new ExcelHeader("vic", "VIC", CellDataType.NUMBER))
                 .header(new ExcelHeader("rox", "ROX", CellDataType.NUMBER))
                 .header(new ExcelHeader("cy5", "Cy5", CellDataType.NUMBER))
                 .rows(detailRows)
                 .option(poiSheet -> applyHeaderBold(poiSheet, 4))
                 .option(poiSheet -> applyColumnWidths(poiSheet, 4, 18))
                 .option(poiSheet -> applyParamHeaderBoldDelayed(poiSheet));
        };
    }
}
